﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using ZhangBY.Config;
using ZhangBY.Common;
using ZhangBY.Entity;

namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {        

        #region 企业新闻
        /// <summary>
        /// 发布企业新闻
        /// </summary>
        /// <returns></returns>
        public int InsertCompanyNews(CompanyNewsInfo comNewsInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,comNewsInfo.UserUid),
                                      DbHelper.MakeInParam("@newsid",(DbType)SqlDbType.NVarChar,60,comNewsInfo.NewsId),
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NVarChar,100,comNewsInfo.NewsTitle),
                                      DbHelper.MakeInParam("@subtitle",(DbType)SqlDbType.NVarChar,100,comNewsInfo.NewsSubtitle),
                                      DbHelper.MakeInParam("@key",(DbType)SqlDbType.NVarChar,100,comNewsInfo.NewsKey),
                                      DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,200,comNewsInfo.NewsDescription),
                                      DbHelper.MakeInParam("@plusher",(DbType)SqlDbType.NChar,20,comNewsInfo.Plusher),
                                      DbHelper.MakeInParam("@takenfrom",(DbType)SqlDbType.NChar,40,comNewsInfo.TakenFrom),
                                      DbHelper.MakeInParam("@author",(DbType)SqlDbType.NChar,20,comNewsInfo.Author),                                     
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,5,comNewsInfo.Audit),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,comNewsInfo.Date.Length,comNewsInfo.Date),
                                      DbHelper.MakeInParam("@published",(DbType)SqlDbType.DateTime,comNewsInfo.Published.Length,comNewsInfo.Published),
                                      DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,comNewsInfo.NewsContent.Length,comNewsInfo.NewsContent)
                                      
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("INSERT INTO [ZhangBY].[dbo].[{0}companynews] ", BaseConfigs.GetTablePrefix);
            commantText.Append("([newsid],[userid],[title],[subtitle],[key],[description],[content],[plusher],[takenfrom],[Author],[Audit],[published],[date])");
            commantText.Append("VALUES(@newsid,@userid,@title,@subtitle,@key,@description,@content,@plusher,@takenfrom,@author,@audit,@published,@date)");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 根据用户ID和newsid更新企业新闻
        /// </summary>
        /// <param name="comNewsInfo"></param>
        /// <returns></returns>
        public int UpdateCompanyNewsByNewsIdAndUserID(CompanyNewsInfo comNewsInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,comNewsInfo.UserUid),
                                      DbHelper.MakeInParam("@newsid",(DbType)SqlDbType.NVarChar,60,comNewsInfo.NewsId),
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NVarChar,100,comNewsInfo.NewsTitle),
                                      DbHelper.MakeInParam("@subtitle",(DbType)SqlDbType.NVarChar,100,comNewsInfo.NewsSubtitle),
                                      DbHelper.MakeInParam("@key",(DbType)SqlDbType.NVarChar,100,comNewsInfo.NewsKey),
                                      DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,200,comNewsInfo.NewsDescription),
                                      DbHelper.MakeInParam("@plusher",(DbType)SqlDbType.NChar,20,comNewsInfo.Plusher),
                                      DbHelper.MakeInParam("@takenfrom",(DbType)SqlDbType.NChar,40,comNewsInfo.TakenFrom),
                                      DbHelper.MakeInParam("@author",(DbType)SqlDbType.NChar,20,comNewsInfo.Author),                                     
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,5,comNewsInfo.Audit),
                                      DbHelper.MakeInParam("@published",(DbType)SqlDbType.DateTime,comNewsInfo.Published.Length,comNewsInfo.Published),
                                      DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,comNewsInfo.NewsContent.Length,comNewsInfo.NewsContent)
                                      
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}companynews] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET [title] =@title ,[subtitle] =@subtitle  ,[key] =@key ,[description] =@description  ,[content] =@content ,");
            commantText.Append("[plusher] =@plusher ,[takenfrom] =@takenfrom,[Author] =@author,[Audit] =@audit,[published] =@published  ");
            commantText.Append(" WHERE [newsid]=@newsid AND[userid]=@userid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        
        /// <summary>
        /// 根据newsid更新企业新闻
        /// </summary>
        /// <param name="comNewsInfo"></param>
        /// <returns></returns>
        public int UpdateCompanyNewsByNewsId(CompanyNewsInfo comNewsInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@newsid",(DbType)SqlDbType.NVarChar,60,comNewsInfo.NewsId),
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NVarChar,100,comNewsInfo.NewsTitle),
                                      DbHelper.MakeInParam("@subtitle",(DbType)SqlDbType.NVarChar,100,comNewsInfo.NewsSubtitle),
                                      DbHelper.MakeInParam("@key",(DbType)SqlDbType.NVarChar,100,comNewsInfo.NewsKey),
                                      DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,200,comNewsInfo.NewsDescription),
                                      DbHelper.MakeInParam("@plusher",(DbType)SqlDbType.NChar,20,comNewsInfo.Plusher),
                                      DbHelper.MakeInParam("@takenfrom",(DbType)SqlDbType.NChar,40,comNewsInfo.TakenFrom),
                                      DbHelper.MakeInParam("@author",(DbType)SqlDbType.NChar,20,comNewsInfo.Author),                                     
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,5,comNewsInfo.Audit),
                                      DbHelper.MakeInParam("@published",(DbType)SqlDbType.DateTime,comNewsInfo.Published.Length,comNewsInfo.Published),
                                      DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,comNewsInfo.NewsContent.Length,comNewsInfo.NewsContent)
                                      
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}companynews] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET [title] =@title ,[subtitle] =@subtitle  ,[key] =@key ,[description] =@description  ,[content] =@content ,");
            commantText.Append("[plusher] =@plusher ,[takenfrom] =@takenfrom,[Author] =@author,[Audit] =@audit,[published] =@published ");
            commantText.Append(" WHERE [newsid]=@newsid" );
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
     
        /// <summary>
        /// 根据USERID和newsid查找企业新闻
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="newsid"></param>
        /// <returns></returns>
        public DataTable GetCompanyNewsByNewsIdAndUserId(string userid,string newsid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@newsid",(DbType)SqlDbType.NVarChar,60,newsid)
                                  };
            string commandText = string.Format("SELECT * FROM [dbo].[{0}companynews] WHERE [userid]=@userid AND [newsid]=@newsid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,parms).Tables[0];
        }
        /// <summary>
        /// 根据PROID查找企业新闻
        /// </summary>
        /// <param name="newsid"></param>
        /// <returns></returns>
        public DataTable GetCompanyNewsByNewsId(string newsid)
        {
            DbParameter parms = DbHelper.MakeInParam("@newsid",(DbType)SqlDbType.NVarChar,60,newsid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}companynews] WHERE [newsid]=@newsid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 根据USERID读取所有企业新闻数据行
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetCompanyNewsByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}companynews] WHERE [userid]=@userid ORDER BY [date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 根据USERID和newsid删除企业新闻
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="newsid"></param>
        /// <returns></returns>
        public int DelCompanyNewsByUserIdAndNewsId(string userid, string newsid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@newsid",(DbType)SqlDbType.NVarChar,60,newsid)
                                  };
            string commandText = string.Format("DELETE FROM [dbo].[{0}companynews] WHERE [userid]=@userid AND [newsid]=@newsid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 根据newsid删除企业新闻
        /// </summary>
        /// <param name="newsid"></param>
        /// <returns></returns>
        public int DelCompanyNewsByNewsId(string newsid)
        {
            DbParameter parms = DbHelper.MakeInParam("@newsid",(DbType)SqlDbType.NVarChar,60,newsid);
            string commandText = string.Format("DELETE FROM [dbo].[{0}companynews] WHERE [newsid]=@newsid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据用户ID取得企业新闻总行数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetCountCompanyNewsByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT COUNT(newsid) FROM [dbo].[{0}companynews] WHERE [userid]=@userid", BaseConfigs.GetTablePrefix);
            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms), 0);
        }
        #endregion
        
    }
}